-- 부서번호 시퀀스 생성 (4자리, 1000부터 시작, 10씩 증가)
drop sequence st_seq_deptno;
create sequence st_seq_deptno start with 1000 increment by 10;
-- 부서 정보 테이블 생성
drop table st_dept;
create table st_dept(
	deptno		NUMBER(4,0)		PRIMARY KEY,
	dname		VARCHAR2(50)		NOT NULL,
	rtdeptno		NUMBER(4,0)		NOT NULL,
	curnum		NUMBER(3,0)		NOT NULL,
	maxnum		NUMBER(3,0)		NOT NULL,
	description	VARCHAR2(1000));
	
-- 직급 번호용 시퀀스
drop sequence st_seq_jobno;
create sequence st_seq_jobno start with 100 increment by 1;
-- 직급 정보 테이블
drop table st_job;
create table st_job(
	jobno		NUMBER(3,0)		PRIMARY KEY,
	jname		VARCHAR2(50)		NOT NULL,
	slevel		NUMBER(2,0)		NOT NULL,
	description	VARCHAR2(1000));

-- 사번용 시퀀스 생성
-- 사번은 년+월+3자리시퀀스 넘버로 생성 (Ex. 1111091)
drop sequence st_seq_sabun;
create sequence st_seq_sabun start with 100 increment by 1 maxvalue 999 cycle;
-- 유저정보 테이블 생성
drop table st_user;
create table st_user( 
	deptno	number(4,0)		constraint user_deptno_fk references st_dept(deptno),
	jobno	number(3,0)		constraint user_jobno_fk references st_job(jobno),
	slevel	number(2,0)		not null,
	sabun	number(7,0)		primary key,
	name	varchar2(100)	not null,
	pwd		varchar2(10)	not null,
	tel		varchar2(15)	not null,
	hp		varchar2(15)	not null,
	fax		varchar2(15),
	email	varchar2(500),
	profile	varchar2(1000),
	imgpath	varchar2(200),
	regdate	date			not null);	
	
-- 주소록 카테고리용 시퀀스
drop sequence st_seq_ab_cno;
create sequence st_seq_ab_cno start with 1 increment by 1;
-- 주소록 카테고리 테이블
drop table st_abcategory;
create table st_abcategory(
	cno number primary key,
	cname varchar2(100),
	description varchar2(1000),
	type number(1,0));	
-- 주소록용 시퀀스
drop sequence st_seq_addrno;
create sequence st_seq_addrno start with 1 increment by 1;
-- 주소록 테이블
drop table st_addrbook;
create table ST_ADDRBOOK	(
	msabun	NUMBER(7,0)		constraint addrbook_msabun_fk references st_user(sabun),
	addrno	NUMBER 				PRIMARY KEY,
	type		NUMBER(1,0)		NOT NULL,	
	name		VARCHAR2(100)	NOT NULL,	
	job		VARCHAR2(100),		
	company	VARCHAR2(100),	
	dept		VARCHAR2(100),		
	email		VARCHAR2(500),
	tel			VARCHAR2(15)	,	
	hp			VARCHAR2(15)	,	
	fax		VARCHAR2(15),	
	msgid		VARCHAR2(500),
	addr		VARCHAR2(1000),		
	birth		DATE	,	
	homepage	VARCHAR2(500),	
	description	VARCHAR2(1000),		
	cno number constraint addrbook_cno_fk references st_abcategory(cno));
	
-- 출결테이블용 시퀀스	
drop sequence st_seq_cno;
create sequence st_seq_cno start with 1 increment by 1;
-- 출결 관리 테이블
drop table st_commute;
create table st_commute(
	sabun	NUMBER(7,0) constraint commute_sabun_fk references st_user(sabun),
	cno		NUMBER	primary key,	
	cwork	DATE,
	lwork	DATE);

-- 일정 관리 테이블용 시퀀스
drop sequence st_seq_sno;
create sequence st_seq_sno start with 1 increment by 1;
-- 일정 관리 테이블
drop table st_schedule;
create table st_schedule(
	sabun		NUMBER(7,0) constraint schedule_sabun_fk references st_user(sabun),	
	sno			NUMBER			primary key,
	PSUBJECT	VARCHAR2(100)	NOT NULL,
	PPLACE		VARCHAR2(100),	
	PCONTENT	VARCHAR2(100),	
	SDAY		varchar2(10),
  SDAY1		varchar2(10),
  SDAY2		varchar2(10),
	EDAY		varchar2(10),
  EDAY1		varchar2(10),
  EDAY2		varchar2(10),
	TYPE		NUMBER(1,0)		NOT NULL);
	
-- 게시물 번호용 시퀀스
drop sequence st_seq_boardno;
create sequence st_seq_boardno start with 1 increment by 1;
-- 공용 게시판 테이블
drop table st_article_tb;
create table st_article_tb(
	boardno	NUMBER	primary key,
	subject		VARCHAR2(1000)	NOT NULL,
	content		VARCHAR2(4000)	NOT NULL,
	reg_date	DATE			DEFAULT SYSDATE,
	sabun		NUMBER(7,0) constraint article_tb_sabun_fk references st_user(sabun),
	readnum		NUMBER			,
	ref	NUMBER					,
	step		NUMBER			,
	jump		NUMBER			,
	root		NUMBER			constraint article_tb_boardno_fk references st_article_tb(boardno),
	depth		NUMBER			,
	filename	VARCHAR2(200),	
	filesize	NUMBER,	
	filetype	VARCHAR2(50),	
	type		NUMBER			NOT NULL,
	deptno		NUMBER);

-- 리플용 시퀀스
drop sequence st_seq_replyno;
create sequence st_seq_replyno start with 1 increment by 1;
-- 리플 테이블
drop table st_reply_tb;
create table st_reply_tb(
	rootno		NUMBER constraint reply_tb_boardno_fk references st_article_tb(boardno),	
	replyno		NUMBER			primary key,
	rcontent 	varchar2(1000) 	not null,
	reg_date	DATE			DEFAULT SYSDATE,
	lev			NUMBER			,
	step		NUMBER			,
	sabun		NUMBER(7,0)		constraint reply_tb_sabun_fk references st_user(sabun));

-- 게시판 타입 정보용 테이블
drop sequence st_seq_btype;
create sequence st_seq_btype start with 1 increment by 1;
drop table st_board_type;
create table st_board_type(
	type	NUMBER			PRIMARY KEY,
	bname	VARCHAR2(100)	NOT NULL,
	wlevel	NUMBER(2,0)		NOT NULL,
	rlevel	NUMBER(2,0)		NOT NULL,
	deptno	NUMBER(4,0)		NOT NULL);
	
-- 업무 관련 테이블들
/*
type
1(요청),2(반려) -> 보낸결제함
3(대기),4(진행),5(완료) -> 받은결제함
*/
drop sequence st_seq_stno;
create sequence st_seq_stno start with 1 increment by 1;
drop table st_settle;
create table st_settle(no number primary key,
		    subject varchar2(100) not null,
		    content varchar2(4000) not null,
		    writeday date default sysdate,	
		    send number(7,0) constraint settle_sabun_fk references st_user(sabun),
		    get number(7,0) constraint settle_sabun_fk2 references st_user(sabun),
		    type number);
---------------------------------------------------------결제

/*
type

2(보낸업무)
3(받은업무)
4(휴지통)
*/
drop sequence st_seq_wcno;
create sequence st_seq_wcno start with 1 increment by 1;
drop table st_workcall;
create table st_workcall(no number primary key,
		    subject varchar2(100) not null,
		    content varchar2(4000) not null,
		    writeday date default sysdate,	
		    send number(7,0) constraint workcall_sabun_fk references st_user(sabun),
		    get number(7,0) constraint workcall_sabun_fk2 references st_user(sabun),
		    type number not null,
		    filename varchar2(50),
		    filetype varchar2(50),
		    filesize number);
----------------------------------------------------------업무연락

/*
type
1(상급자가 확인전)
2(상급자가 확인후)
*/
		    
/*
depttype - deptno
DEPTNO	DNAME	
1810	총무1-1팀	
1230	총무1팀	
2000	기획1팀	
2010	총무1팀	
1000	기획부	
1010	총무부		
1020	경리부	
1030	고객만족실
1040	IT사업부
1050	기계사업부
1060	기술연구부
1070	관리본부	
1080	네트워크사업부	
1090	해외사업부	
1100	전략기획부
1110	법무부	
1220	기획1팀	
*/		    
drop sequence st_seq_wsno;
create sequence st_seq_wsno start with 1 increment by 1;
drop table st_worksheet;
create table st_worksheet(no number primary key,
		    subject varchar2(100) not null,
		    content varchar2(4000) not null,
		    writeday date default sysdate,	
		    writer number(7,0) constraint worksheet_sabun_fk references st_user(sabun),
		    depttype number(7,0) constraint worksheet_deptno_fk2 references st_dept(deptno),
		    type number);		    
insert into st_worksheet values(st_seq_wsno.nextval,'하이','ㅋㅋ',sysdate,3333333,1110,1);		    
--------------------------------------------------------업무일지

/*
type
0(승인실패 및 보류-작성자및 승인자에게 한 함)
1(보고서-신규보고서)
2(보고서-1차승인)
3(보고서-최종승인)
*/
drop sequence st_seq_wrno;
create sequence st_seq_wrno start with 1 increment by 1;			  
drop table st_workreport;
create table st_workreport(no number primary key,
		    subject varchar2(100) not null,
		    content varchar2(4000) not null,
		    writeday date default sysdate,	
		    send number(7,0) constraint workreport_sabun_fk references st_user(sabun),
		    get number(7,0) constraint workreport_sabun_fk2 references st_user(sabun),
		    agreer number(7,0) constraint workreport_sabun_fk3 references st_user(sabun),
		    type number);
--------------------------------------------------------업무보고	

-- 로그인 히스토리 시퀀스
drop sequence st_seq_history;
create sequence st_seq_history start with 1 increment by 1;		    
--로그인 히스토리  
create table st_login_history(
	no number primary key,
	sabun number(7,0) constraint history_sabun_fk references st_user(sabun),
	lgtime date default sysdate,
	lgip varchar2(12) not null,
	status varchar2(10) not null
); 

-- 데이터 삭제 순서
delete from st_workcall;
delete from st_workreport;
delete from st_worksheet;
delete from st_settle;
delete from st_commute;
delete from st_schedule;
delete from st_dept;
delete from st_job;
delete from st_user;		    